package com.ruoyi.operation.task;

import com.ruoyi.common.utils.DruidConnection;
import com.ruoyi.operation.domain.StudentInfo;
import com.ruoyi.operation.service.IStudentInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 定时任务调度测试
 * 
 * @author ruoyi
 */
@Component("operationTask")
public class OperationTask
{
    @Autowired
    private IStudentInfoService studentInfoService;

    public void syscStudent()
    {
        Date date = new Date();
        //String sql = "SELECT * FROM users WHERE id = ?";
        List<StudentInfo> list = new ArrayList();
        //String sql = "select orderid, username, password, school from  ceshia.wkorder where plat = 6 order by id desc";
        String sql = "SELECT a.orderid, a.username, a.password, a.school\n" +
                "FROM ceshia.wkorder a\n" +
                "WHERE NOT EXISTS (\n" +
                "    SELECT 1\n" +
                "    FROM lesson_pro.student_info b\n" +
                "    WHERE a.username = b.zh and a.school = b.xx\n" +
                ") and a.plat = 6 and school in ('河北地质大学','辽宁科技大学','山东交通学院');";
        try (Connection conn = DruidConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
             //设置参数
             //pstmt.setInt(1, 1);
             try (ResultSet rs = pstmt.executeQuery()) {
                 while (rs.next()) {
                     StudentInfo studentInfo = new StudentInfo();
                     studentInfo.setYhid(1L);
                     studentInfo.setYhmc("admin");
                     studentInfo.setXx(rs.getString("school"));
                     studentInfo.setZh(rs.getString("username"));
                     studentInfo.setMm(rs.getString("password"));
                     studentInfo.setCjsj(date);
                     studentInfo.setKczt("0");
                     studentInfo.setZtxx("排队中");
                     studentInfo.setKcfw("全部课程");
                     studentInfo.setDdh(rs.getString("orderid"));
                     studentInfoService.insertStudentInfo(studentInfo);
                }
             }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidConnection.close();
        }
    }

}
